package com.imist.demo;

import org.junit.Test;

import java.sql.*;


public class JDBCDemo1 {
    /**
     * jdbc入门程序
     * <p>
     * 注意：1.原包com.mysql.jdbc.Driver;已经被弃用，新的包通过SPI自动注册驱动程序，通常不需要手动加载驱动程序类
     * 2. useSSL=false 不通过证书认证，不然高版本会有警告信息；
     */
    @Test
    public void demo1() {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // 1.注册驱动
            //DriverManager.registerDriver(new Driver());//会导致驱动注册两次
            // 因为驱动类在加载的时候就会取注册驱动，所以通过这种方式注册
            Class.forName("com.mysql.cj.jdbc.Driver"); //这里注意一下版本  低版本用 com.mysql.jdbc.Driver
            // 2. 获得连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test?useSSL=false", "root", "123456");
            String sql = "select * from user";
            // 3. 创建执行SQL语句的对象，并且执行SQL
            statement = conn.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                int uid = resultSet.getInt("uid");
                String username = resultSet.getString("username");
                String password = resultSet.getString("password");
                String name = resultSet.getString("name");
                System.out.println(uid + "   " + username + "    " + password + "    " + name);
            }
            // 4. 关闭连接 ，释放资源；
            resultSet.close();
            statement.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                }
                resultSet = null;
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                }
                statement = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
                conn = null; //垃圾回收机制会更早回收对象
            }
        }
    }

    @Test
    public void code2() {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc_test?useSSL=false", "root", "123456");
            statement = conn.createStatement();
            String sql = "select * from goods where price < 3500";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                float price = resultSet.getFloat("price");
                String desp = resultSet.getString("desp");
                System.out.println(name + "   " + price + "     " + desp);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                }
                resultSet = null;
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                }
                statement = null;
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
                conn = null; //垃圾回收机制会更早回收对象
            }

        }
    }
}
